﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices.ComTypes;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WinForm1.db
{
    internal class DBSQLite
    {
        public static String Write(String exPath, List<PathInfos> fileList)
        {
            if (!Directory.Exists(exPath))
            {
                try
                {
                    Directory.CreateDirectory(exPath);
                }
                catch
                {
                    return null;
                }
            }
            if (exPath.LastIndexOf("\\") != exPath.Length - 1)
            {
                exPath += "\\";
            }
            //String filePath = exPath + System.DateTime.Now.ToString().Replace(":", "-").Replace("/", "-") + " CSVResult.db";
            String filePath = exPath + "Result.db";
            String connstr = "Data source=" + filePath + ";version=3;";
            try
            {
                SQLiteConnection.CreateFile(filePath);
            }
            catch
            {

            }
            using (var conn = new SQLiteConnection(connstr))
            {
                conn.Open();
                var createTable = @"CREATE TABLE IF NOT EXISTS FileInfos(
                                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                                        fileName TEXT,
                                        filePath TEXT,
                                        fileType TEXT,
                                        fileSize TEXT,
                                        fileDate TEXT)";
                using (var ctc = new SQLiteCommand(createTable, conn))
                {
                    ctc.ExecuteNonQuery();
                }
                var clear = "delete from FileInfos";
                using (var cc = new SQLiteCommand(clear, conn))
                {
                    cc.ExecuteNonQuery();
                }
                foreach (var fileInfo in fileList)
                {

                    var insert = @"INSERT INTO FileInfos(fileName,filePath,fileType,fileSize,fileDate)
                                                VALUES(@Name,@Path,@Type,@Size,@Date)";
                    using (var ic = new SQLiteCommand(insert, conn))
                    {
                        int index = fileInfo.FilePath.LastIndexOf("\\");
                        ic.Parameters.AddWithValue("@Name", fileInfo.FileName);
                        ic.Parameters.AddWithValue("@Path", fileInfo.FilePath.Substring(0, index + 1));
                        ic.Parameters.AddWithValue("@Type", fileInfo.FileType);
                        ic.Parameters.AddWithValue("@Size", Math.Round(fileInfo.FileSize / 1024, 2) + "KB");
                        ic.Parameters.AddWithValue("@Date", fileInfo.FileDate.ToString());
                        ic.ExecuteNonQuery();
                    }
                }
            }
            return filePath;
        }

        public static int Read(String imPath, ref List<PathInfos> fileList)
        {
            if (!File.Exists(imPath))
            {
                return 0;
            }
            String connstr = "Data source=" + imPath + ";version=3;";
            try
            {
                using (var conn = new SQLiteConnection(connstr))
                {

                    conn.Open();
                    var select = "SELECT fileName,filePath,fileType,fileSize,fileDate from FileInfos";
                    using (var sc = new SQLiteCommand(select, conn))
                    {
                        var sr = sc.ExecuteReader();
                        fileList.Clear();
                        while (sr.Read())
                        {
                            //MessageBox.Show(sr.GetString(0));
                            string fileName = sr.GetString(0);
                            string filePath = sr.GetString(1);
                            string fileType = sr.GetString(2);
                            Double fileSize = Double.Parse(sr.GetString(3).Substring(0, sr.GetString(3).Length - 2));
                            DateTime fileDate = DateTime.Parse(sr.GetString(4));
                            PathInfos pathInfos = new PathInfos(fileName, filePath, fileType, fileSize, fileDate);
                            fileList.Add(pathInfos);
                        }
                    }
                }
            }
            catch
            {
                return 0;
            }
            return fileList.Count;
        }

        public static void Close(ref SQLiteConnection conn)
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
}
